﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data ;
using System.Data .SqlClient ;
using dhDataAcess.dataAcess .sysDataSetTableAdapters ;
using dhDataAcess.Model ;

namespace dhDataAcess.sysDAL
{
   public class workerListDAL
    {
       string tableName = "workerList";
        private SqlTransaction tran = null;
        private SqlConnection conn = null;
        public workerListDAL(SqlTransaction _transaction, SqlConnection _conn)
        {
            tran = _transaction;
            conn=_conn;
        }
      
        /// <summary>
        /// 插入一行职员表数据
        /// </summary>
        /// <param name="deptRow"></param>
        /// <returns></returns>

        public int workerListInsert(workerListModel deptRow)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string insertStr = baseBLL.dTableColumnRead.insertSqlStr(tableName).ToString();
            //string insertStr = "insert into workerList (mark,updateUser,updateDate,createUser,createDate,detpNum,title,birthday,cardnum,sex,name,workerNum) values(@mark,@updateUser,@updateDate,@createUser,@createDate,@detpNum,@title,@birthday,@cardnum,@sex,@name,@workerNum)";

            SqlParameter[] para = {
                                 new SqlParameter ("@mark",deptRow .mark ),
                                 new SqlParameter ("@updateUser",deptRow .updateUser),
                                 new SqlParameter ("@updateDate",deptRow .updateDate),
                                 new SqlParameter ("@createUser",deptRow .createUser),
                                 new SqlParameter ("@createDate",deptRow .createDate),
                                 new SqlParameter ("@detpNum",deptRow .detpNum),
                                 new SqlParameter ("@title",deptRow .title ),
                                 new SqlParameter ("@birthday",deptRow .birthday ),
                                 new SqlParameter ("@cardnum",deptRow .cardnum ),
                                 new SqlParameter ("@sex",deptRow .sex ),
                                 new SqlParameter ("@name",deptRow .name ),
                                 new SqlParameter ("@workerNum",deptRow .workerNum )
                                   };

            cmd.Parameters.AddRange(para);
            cmd.CommandText = insertStr;
            cmd.Transaction = tran;
            int i = cmd.ExecuteNonQuery();
            return i;
        }

        /// <summary>
        /// 更新一行职员表数据 
        /// </summary>
        /// <param name="deptRow"></param>
        /// <returns></returns>

        public int workerListUpdate(workerListModel deptRow)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string updateStr = baseBLL.dTableColumnRead.updateSqlStr(tableName).ToString();
           // string updateStr = "update workerList set mark=@mark,updateUser=@updateUser,updateDate=@updateDate,createUser=@createUser,createDate=@createDate,detpNum=@detpNum,title=@title,birthday=@birthday,cardnum=@cardnum,sex=@sex,name=@name where workerNum=@workerNum";


            SqlParameter[] para = {
                                 new SqlParameter ("@mark",deptRow .mark ),
                                 new SqlParameter ("@updateUser",deptRow .updateUser),
                                 new SqlParameter ("@updateDate",deptRow .updateDate),
                                 new SqlParameter ("@createUser",deptRow .createUser),
                                 new SqlParameter ("@createDate",deptRow .createDate),
                                 new SqlParameter ("@detpNum",deptRow .detpNum),
                                 new SqlParameter ("@title",deptRow .title ),
                                 new SqlParameter ("@birthday",deptRow .birthday ),
                                 new SqlParameter ("@cardnum",deptRow .cardnum ),
                                 new SqlParameter ("@sex",deptRow .sex ),
                                 new SqlParameter ("@name",deptRow .name ),
                                 new SqlParameter ("@workerNum",deptRow .workerNum )
                                   };
           
            cmd.Parameters.AddRange(para);
            cmd.CommandText = updateStr;
            cmd.Transaction = tran;
            int i = cmd.ExecuteNonQuery();
            return i;
        }


        /// <summary>
        /// 删除一行职员表数据
        /// </summary>
        /// <param name="workerNum"></param>
        /// <returns></returns>
 
        public int workerListDel(string workerNum)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string deleteStr = baseBLL.dTableColumnRead.delSqlStr(tableName).ToString();
            //string deleteStr = "delete workerList where workerNum=@workerNum";
            SqlParameter[] para = {
                                new SqlParameter ("@workerNum",workerNum)
                              };

            cmd.Parameters.AddRange(para);
            cmd.CommandText = deleteStr;
            cmd.Transaction = tran;
            int i = cmd.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 根据职员工号返回职员姓名
        /// </summary>
        /// <param name="workerNum"></param>
        /// <returns></returns>

        public string workerListmoduleName(string workerNum)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string selectStr = "selete name from workerList where workerNum=@workerNum";
            SqlParameter[] para = {
                                new SqlParameter ("@workerNum",workerNum),
                              };

            cmd.Parameters.AddRange(para);
            cmd.CommandText = selectStr;
            cmd.Transaction = tran;
            SqlDataReader rdr = cmd.ExecuteReader();
            string name = null;
            while (rdr.Read())
            {
                name = rdr[0].ToString();
            }

            return name;
        }
    }
}
